# encoding: UTF-8
# user：admin at 2018/10/18

import xlrd
from excel2Sqlite import read2Sqlite
from openpyxl import load_workbook
import datetime

class ReadExcel():

    def readExcel_by_Openpyxl(self,path,filename):
        dicData={}
        rSqlite = read2Sqlite()

        filepath = path + '/' + filename
        dicData['name'] = filename.split('-')[1].split('.')[0]

        data=load_workbook(filepath)
        yanglao=data.get_sheet_by_name('养老保险')
        yiliao = data.get_sheet_by_name('医疗保险')
        gongshang = data.get_sheet_by_name('工伤保险')
        shiye = data.get_sheet_by_name('失业保险')
        shengyu = data.get_sheet_by_name('生育保险')

        for row in range(1,yanglao.max_row+1):
            #print(yanglao[row][0].value)
            if yanglao[row][2].value=='113804':
                fdate=yanglao[row][1].value
                dicData['fdate'] = fdate
                dicData['fyanglaoje'] =float(yanglao[row][7].value)

                if yiliao.max_row - 3 >= row:
                    if yiliao[row][2].value == '113804' and yiliao[row][1].value == fdate:
                        dicData['fyiliaoje'] = float(yiliao[row][8].value)
                    else:
                        dicData['fyiliaoje'] = 0
                else:
                    dicData['fyiliaoje'] = 0

                if gongshang.max_row - 3 >= row:
                    if gongshang[row][2].value == '113804' and gongshang[row][1].value == fdate:
                        dicData['fgongshangje'] = float(gongshang[row][7].value)
                    else:
                        dicData['fgongshangje'] = 0
                else:
                    dicData['fgongshangje'] = 0

                if shiye.max_row - 3 >= row:
                    if shiye[row][2].value == '113804' and shiye[row][1].value == fdate:
                        dicData['fshiyeje'] = float(shiye[row][7].value)
                    else:
                        dicData['fshiyeje'] = 0
                else:
                    dicData['fshiyeje'] = 0

                if shengyu.max_row - 3 >= row:
                    if shengyu[row][2].value == '113804'  and shengyu[row][1].value == fdate:
                        dicData['fshengyuje'] = float(shengyu[row][7].value)
                    else:
                        dicData['fshengyuje'] = 0
                else:
                    dicData['fshengyuje'] = 0

                result, message = rSqlite.writeDB(dicData)
                if result == True:
                    if message!='成功':
                        print(message)
                else:
                    print('【{}】的【{}】社保数据写入数据库错误,错误原因为：{}'.format(dicData['name'], dicData['fdate'], message))
            else:
                continue

    def read_Excel(self,path,filename):
        dicData = {}
        rSqlite = read2Sqlite()

        filepath = path + '/' + filename
        dicData['name'] = filename.split('-')[1].split('.')[0]

        data = xlrd.open_workbook(filepath)

        yanglao = data.sheet_by_name('养老保险')
        yiliao = data.sheet_by_name('医疗保险')
        gongshang = data.sheet_by_name('工伤保险')
        shiye = data.sheet_by_name('失业保险')
        shengyu = data.sheet_by_name('生育保险')

        for nrows_one in range(1, yanglao.nrows):

            if yanglao.cell_value(nrows_one, 2) == '113804':
                fdate=yanglao.cell_value(nrows_one, 1)
                dicData['fdate'] = fdate
                dicData['fyanglaoje'] = float(yanglao.cell_value(nrows_one, 7))

                if yiliao.nrows - 3 >= nrows_one:
                    if yiliao.cell_value(nrows_one, 2) == '113804' and yiliao.cell_value(nrows_one, 1) == fdate:
                        dicData['fyiliaoje'] = float(yiliao.cell_value(nrows_one, 8))
                    else:
                        dicData['fyiliaoje'] = 0
                else:
                    dicData['fyiliaoje'] = 0

                if gongshang.nrows - 3 >= nrows_one:
                    if gongshang.cell_value(nrows_one, 2) == '113804' and gongshang.cell_value(nrows_one, 1) == fdate:
                        dicData['fgongshangje'] = float(gongshang.cell_value(nrows_one, 7))
                    else:
                        dicData['fgongshangje'] = 0
                else:
                    dicData['fgongshangje'] = 0

                if shiye.nrows - 3 >= nrows_one:
                    if shiye.cell_value(nrows_one, 2) == '113804' and shiye.cell_value(nrows_one, 1) == fdate:
                        dicData['fshiyeje'] = float(shiye.cell_value(nrows_one, 7))
                    else:
                        dicData['fshiyeje'] = 0
                else:
                    dicData['fshiyeje'] = 0

                if shengyu.nrows - 3 >= nrows_one:
                    if shengyu.cell_value(nrows_one, 2) == '113804'  and shengyu.cell_value(nrows_one, 1) == fdate:
                        dicData['fshengyuje'] = float(shengyu.cell_value(nrows_one, 7))
                    else:
                        dicData['fshengyuje'] = 0
                else:
                    dicData['fshengyuje'] = 0

                result, message = rSqlite.writeDB(dicData)
                if result == True:
                    pass
                else:
                    print('【{}】的【{}】社保数据写入数据库错误,错误原因为：{}'.format(dicData['name'], dicData['fdate'], message))
            else:
                continue

    def write_Excel(self,model):
        timeName=datetime.datetime.now().strftime('%Y-%m-%d')
        templateFile='./model/template.xlsx'
        savePath='./data/'

        try:
            wb=load_workbook(templateFile)
            sheet=wb.get_sheet_by_name('Sheet1')

            #print("{}行，{}列".format(model.rowCount(), model.columnCount()))
            #print(data.text())

            for r in range(model.rowCount()):
                #print(model.item(r,1).text())

                sheet[r + 2][0].value = model.item(r, 0).text()
                sheet[r + 2][1].value = model.item(r, 1).text()
                sheet[r + 2][2].value = float(model.item(r, 2).text())
                sheet[r + 2][3].value = float(model.item(r, 3).text())
                sheet[r + 2][4].value = float(model.item(r, 4).text())
                sheet[r + 2][5].value = float(model.item(r, 5).text())
                sheet[r + 2][6].value = float(model.item(r, 6).text())
                sheet[r + 2][7].value = float(model.item(r, 7).text())

            wb.save(savePath+'/员工社保缴交明细（{}）.xlsx'.format(timeName))
            wb.close()
            return True,''
        except Exception as errorMsg:
            return False,errorMsg